﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using DataBaseLibrary.DBInterfaces;
using xdb = FirebirdSql.Data.FirebirdClient;
using System.Globalization;

namespace DataBaseLibrary.DBObjectsFB
{

    /// <summary>
    /// Класс описывает логику проекции объектов приложения на сущности БД
    /// </summary>
    public class DBFObject 
    {
        protected Dictionary<string, object> Parameters;
        protected string NameTable;
        protected string ID = null; //Идентификатор (ключ) записи в БД данного объекта!
        public xdb.FbCommand command = null;


        public DBFObject()
        {
            ID = null;
        }

        private String ConvertObjectToString(object value)
        {
            if (value is int)
                return value.ToString();
            else if (value is DateTime)
                return String.Format("'{0}.{1}.{2}'", ((DateTime)value).Day.ToString("00"), ((DateTime)value).Month.ToString("00"), ((DateTime)value).Year.ToString("0000"));
            else if (value is string)
                return String.Format("'{0}'", value);
            else if (value is double || value is float)
                return Convert.ToDouble(value).ToString("0.00", CultureInfo.CreateSpecificCulture("en-GB"));
            else return value.ToString();
            
        }

        public Object SaveObject()
        {
            command = FDBProvider.command_static;
            
            if (Parameters == null || command == null || Parameters.Count == 0)
                return null;

            //Если ID не нулл (то есть объект загружен и существует!), то просто возвращаем ID без попытки записать в БД и обновляем объект в БД!!!
            if (ID != null)
            {
                UpdateObject();
                return ID;
            }
                

            StringBuilder FieldString = new StringBuilder();
            StringBuilder ParametersString = new StringBuilder();

            foreach (string fields in Parameters.Keys)
            {
                if (Parameters[fields] == null)
                    continue;
                FieldString.Append(fields + ", ");
                ParametersString.Append(ConvertObjectToString(Parameters[fields]) + ", ");
            }
            FieldString.Remove(FieldString.Length - 2, 2);
            ParametersString.Remove(ParametersString.Length - 2, 2);

            //Сохранение в БД
            command.CommandText = String.Format("insert into {0} (ID, {1}) values (@ID, {2}) returning ID;", NameTable, FieldString, ParametersString);
            command.Parameters.Add("ID", xdb.FbDbType.Integer).Value = 0;
            object return_id = command.ExecuteScalar();
            this.ID = return_id.ToString();
            return return_id;
        }

        public void LoadObject(object ID)
        {
            command = FDBProvider.command_static;
            
            if (Parameters == null || command == null || Parameters.Count == 0 || ID == null)
                return; //TODO: Генерировать exception!

            StringBuilder FieldString = new StringBuilder();
            
            foreach (string fields in Parameters.Keys)
                FieldString.Append(fields + ", ");
            FieldString.Remove(FieldString.Length - 2, 2);

            String id_string = "";
            if (ID as String != null)
                id_string = "'" + ID.ToString() + "'";
            else id_string = ID.ToString();

            command.CommandText = String.Format("select ID, {0} from {1} where ID = {2}", FieldString, NameTable, id_string);
            var reader = command.ExecuteReader();

            List<String> keys = Parameters.Keys.ToList();

            int count_rows = 0;
            
            while (reader.Read())
            {
                foreach (string key in keys)
                {
                    
                    if (reader[key].GetType() == typeof(System.DateTime))
                    {
                        if (reader[key] != null)
                        {
                            System.DateTime d_time = (System.DateTime) reader[key];
                            Parameters[key] = String.Format("{0}.{1}.{2}", d_time.Day.ToString("00"),
                                d_time.Month.ToString("00"), d_time.Year.ToString("0000"));
                        }
                        else
                        {
                            Parameters[key] = null;
                        }
                    }                        
                    else
                        Parameters[key] = reader[key];                    
                }
                this.ID = reader["ID"].ToString();

                count_rows++;
            }

            if (count_rows == 0)
            {
                throw new SystemException(String.Format("Запиь с номером {0} не найдена!", ID));
            }
        }

        public void UpdateObject()
        {

            command = FDBProvider.command_static;
            if (ID == null)
                throw new SystemException("Не задан параметр ID");

            if (Parameters == null || command == null || Parameters.Count == 0)
                return;
            
            StringBuilder ColumnsString = new StringBuilder();

            foreach (string fields in Parameters.Keys)
            {
                if (Parameters[fields] == null)
                    continue;
                ColumnsString.Append(String.Format("{0}={1}, ", fields, ConvertObjectToString(Parameters[fields])));
            }

            ColumnsString.Remove(ColumnsString.Length - 2, 2);

            String id_string = "";
            if (ID as String != null)
                id_string = "'" + ID.ToString() + "'";
            else id_string = ID.ToString();

            command.CommandText = String.Format("update {0} set {1} where ID = {2}; ", NameTable, ColumnsString, id_string);
            command.ExecuteNonQuery();
        }

        public object GetID()
        {
            return ID;
        }
    }
}
